package week07;

import java.io.BufferedOutputStream;
import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.OutputStream;
import java.nio.charset.StandardCharsets;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.sql.Statement;

/**
 * @author edward
 * @since 2021-08-07
 */
public class MillionRecordsInsertion {

    private static final int RECORDS = 1_000_000;

    static {
        try {
            Class.forName("com.mysql.cj.jdbc.Driver");
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }
    }

    public static void main(String[] args) throws SQLException {
//        timeCostApply(MillionRecordsInsertion::statementInsert);         // 256s
//        timeCostApply(MillionRecordsInsertion::preparedStatementInsert); // 236s
//        timeCostApply(MillionRecordsInsertion::batchInsert);             // 134s
//        timeCostApply(MillionRecordsInsertion::multiValuesInsert);       // 12s
        timeCostApply(MillionRecordsInsertion::loadData);                  // 10s
    }

    private static void statementInsert() throws SQLException {
        try (Connection conn = createConnection();
             Statement statement = conn.createStatement()) {
            conn.setAutoCommit(false);
            for (int i = 1; i <= RECORDS; i++) {
                statement.execute(
                        String.format("insert into t_test_product(product_id, product_name, category)" +
                                "values ('%s','%s','%s')", (RECORDS + i), "商品" + i, "类型" + i));
                if (i % 10_000 == 0) {
                    conn.commit();
                }
            }
            conn.commit();
        }
        System.out.print("Statement Insert Finished, ");
    }

    private static void preparedStatementInsert() throws SQLException {
        try (Connection conn = createConnection();
             PreparedStatement ps = conn.prepareStatement(
                     "insert into t_test_product(product_id, product_name, category) values (?, ?, ?)")) {
            conn.setAutoCommit(false);
            for (int i = 1; i <= RECORDS; i++) {
                ps.setString(1, String.valueOf(RECORDS + i));
                ps.setString(2, "商品" + i);
                ps.setString(3, "类型" + i);
                ps.execute();

                if (i % 10_000 == 0) {
                    conn.commit();
                }
            }
            conn.commit();
        }
        System.out.print("PreparedStatement Insert Finished, ");
    }
    private static void batchInsert() throws SQLException {
        try (Connection conn = createConnection();
             PreparedStatement ps = conn.prepareStatement(
                     "insert into t_test_product(product_id, product_name, category) values (?, ?, ?)")) {
            conn.setAutoCommit(false);
            for (int i = 1; i <= RECORDS; i++) {
                ps.setString(1, String.valueOf(RECORDS + i));
                ps.setString(2, "商品" + i);
                ps.setString(3, "类型" + i);
                ps.addBatch();

                if (i % 10_000 == 0) {
                    ps.executeBatch();
                    conn.commit();
                }
            }
            ps.executeBatch();
            conn.commit();
        }
        System.out.print("Batch Insert Finished, ");
        // Time cost:134s
    }
    private static void multiValuesInsert() throws SQLException {
        try (Connection conn = createConnection();
             Statement statement = conn.createStatement()) {
            conn.setAutoCommit(false);
            String insert = "insert into t_test_product(product_id, product_name, category) values";
            StringBuilder builder = new StringBuilder(insert);
            StringBuilder values = new StringBuilder(100_000);
            for (int i = 1; i <= RECORDS; i++) {
                values.append(String.format("('%s','%s','%s'),", (RECORDS + i), "商品" + i, "类型" + i));

                if (i % 100_000 == 0) {
                    builder.append(values);
                    statement.execute(builder.toString().replaceFirst(",\\s*$", ""));
                    conn.commit();
                    builder = new StringBuilder(insert);
                    values = new StringBuilder(1_0000);
                }
            }
            conn.commit();
        }
        System.out.print("MultiValues Insert Finished, ");
    }
    private static void loadData() throws SQLException {
        File inFile = new File("data.csv");
        try {
            inFile.createNewFile();
            try (OutputStream out = new BufferedOutputStream(new FileOutputStream(inFile))) {
                for (int i=1; i <= RECORDS; i++) {
                    out.write(String.format("\"%s\",\"%s\",\"%s\",%d,%d\r\n", (RECORDS + i), "商品" + i, "类型" + i, i * 10, i)
                            .getBytes(StandardCharsets.UTF_8));
                }
                out.flush();
            }
            try (Connection conn = createConnection();
                 Statement statement = conn.createStatement()) {
                conn.setAutoCommit(false);
                statement.execute(String.format(
                        "LOAD DATA INFILE '%s' INTO TABLE t_test_product FIELDS TERMINATED BY ','" +
                                " (product_id, product_name, category, price, quantity)",
                        inFile.getAbsoluteFile())
                );
                conn.commit();
            }
        } catch (IOException e) {
            e.printStackTrace();
        } finally {
            inFile.delete();
        }
        System.out.print("Load Data Insert Finished, ");
    }

    private static Connection createConnection() throws SQLException {
        return DriverManager.getConnection("jdbc:mysql://127.0.0.1:3301/camp", "root", "");
    }

    private static void truncateTable() throws SQLException {
        try (Connection conn = createConnection();
             Statement statement = conn.createStatement()) {
            conn.setAutoCommit(false);
            statement.execute("truncate table t_test_product");
            conn.commit();
        }
    }

    private static void timeCostApply(JdbcFunction func) throws SQLException {
        long start = System.currentTimeMillis();
        try {
            func.inserts();
        } finally {
            System.out.println("Time cost:" + (System.currentTimeMillis() - start)/1000 + "s");
            truncateTable();
        }
    }

    @FunctionalInterface
    private interface JdbcFunction {
        void inserts() throws SQLException;
    }
}
